package com.ele.Dao.DaoImpl;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.ele.Dao.BusinessDao;
import com.ele.Pojo.Business;

import com.ele.Utils.JDBCUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class BusinessDaoImp implements BusinessDao {
    private DataSource dataSource = JDBCUtils.getDs();


    /**
     * 加载所有对象
     */
    public List<Business> getAllBusiness() throws SQLException {
        Connection conn =JDBCUtils.getConn();
        String sql ="select * from business";
        PreparedStatement stmt =conn.prepareStatement(sql);
        ResultSet x =stmt.executeQuery();
        List<Business> businesses = new ArrayList<>();
        while (x.next()){
            Business business =new Business();
            business.setId(x.getInt("id"));
            business.setAccount(x.getString("account"));
            business.setPassword(x.getString("password"));
            business.setTelephone(x.getString("telephone"));
            business.setRegTime(x.getDate("reg_time"));
            business.setContact(x.getString("contact"));

            businesses.add(business);

        }
        stmt.close();
        conn.close();
        return businesses;
    }

    /**
     * 注册添加
     */
    @Override
    public Integer businessRegister(String account, String password, String telephone, String contact) throws SQLException {
        DataSource ds =JDBCUtils.getDs();
        return Db.use(ds).insert(Entity.create("business")
                .set("account",account)
                .set("password",password)
                .set("telephone",telephone)
                .set("contact",contact)
                .set("reg_time",new Date())
        );

    }
    //登录
    public Business businessLogin(String account,String password) throws SQLException {
        Connection conn =JDBCUtils.getConn();
        String sql ="select * from business where account=? and password=?";
        PreparedStatement stmt =conn.prepareStatement(sql);
        stmt.setString(1,account);
        stmt.setString(2,password);
        ResultSet x =stmt.executeQuery();
        Business business =new Business();
        if(x.next()){
           business.setAccount(x.getString("account"));
            business.setId(x.getInt("id"));
            business.setPassword(x.getString("password"));
            business.setContact(x.getString("contact"));
            business.setTelephone(x.getString("telephone"));
            business.setRegTime(x.getDate("reg_time"));
            stmt.close();
            conn.close();
            return business;
        }
        return null;
    }
    /**
     * 修改
     */
    public Integer UpdateBusiness(String account,String password,String telephone,String contact ,String id) throws SQLException {
        Connection conn=JDBCUtils.getConn();
        String sql = "update business set account = ? , password = ? , contact = ? , telephone = ? where id = ?";
        PreparedStatement stmt =conn.prepareStatement(sql);
        stmt.setString(1,account);
        stmt.setString(2,password);
        stmt.setString(3,contact);
        stmt.setString(4,telephone);
        stmt.setString(5,id);
        int x =stmt.executeUpdate();
        stmt.close();
        conn.close();
        return x;
    }
    /**
     * 删除
     */
    public Integer deleteBusiness(String id) throws SQLException {
        Connection conn=JDBCUtils.getConn();

        String sql ="delete  from business where id=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1,id);
        //System.out.println(id);
        int x =stmt.executeUpdate();
        stmt.close();
        conn.close();
        return x;
    }

    /**
     * 根据条件查 功能不健全
     * @param account
     * @param telephone
     * @param contact
     * @return
     * @throws SQLException
     */
    @Override
    public List<Business> queryBusiness(String account, String telephone, String contact) throws SQLException {
        Connection conn =JDBCUtils.getConn();
        String sql ="select * from business where account like ? or telephone like ? or contact like ?";
        PreparedStatement stmt =conn.prepareStatement(sql);
        stmt.setString(1,"%"+account+"%");
        stmt.setString(2,"%"+telephone+"%");
        stmt.setString(3,"%"+contact+"%");
        ResultSet x =stmt.executeQuery();
        List<Business> businesses =new ArrayList<>();
        while (x.next()){
            Business business = new Business();
            business.setId(x.getInt("id"));
            business.setAccount(x.getString("account"));
            business.setPassword(x.getString("password"));
            business.setTelephone(x.getString("telephone"));
            business.setContact(x.getString("contact"));
            business.setRegTime(x.getDate("reg_time"));
            businesses.add(business);
        }
        stmt.close();
        conn.close();
        return businesses;
    }


    /**
     *
     *      客户端给后端程序传递数据的时候，
     *      会传递当前需要页码currentPage和每页要显示pageSize的数据行数
     *     在sql中，需要的查询的起始下标（位置）和每页显示的行数
     *
     *     分析：
     *      currentPage = 1  表示查询第一页的数据    0 , 2
     *      currentPage = 2  表示查询第二页的数据    2 , 2
     *      currentPage = 3  表示查询第二页的数据    4 , 2
     *      currentPage = 4  表示查询第二页的数据    6 , 2
     *      currentPage = 5  表示查询第二页的数据    8 , 2
     *      起始位置与页码之间的关系：
     *      (currentPage - 1) * pageSize
     *
     *      SELECT from business LIMIT 0,2
     *      select * from business LIMIT 2,2
     */
    public List<Business> findAll(String account , String contact, String telephone,String currentPage,String pageSize) throws SQLException {
        String sql = "select * from business ";
        // 定义一个Map集合，将搜索条件对应的列名作为map的key，搜索条件的值作为map的value
        Map<String ,String> map = new HashMap<String ,String>();
        // 判断传递的参数
        if(StrUtil.isNotBlank(account)){
            map.put("account",account);
        }
        if(StrUtil.isNotBlank(contact)){
            map.put("contact",contact);
        }
        if(StrUtil.isNotBlank(telephone)){
            map.put("telephone",telephone);
        }
        // 这里可以直接判断map是否位空
        if(!map.isEmpty()){
            sql = sql + " where ";
            // 判断成立，说明map中有搜索的条件数据，这时只需要遍历map，去给sql中拼接条件即可
            Set<Map.Entry<String, String>> entrySet = map.entrySet();
            for (Map.Entry<String, String> entry : entrySet) {
                sql = sql + entry.getKey() +" like " + "'%"+ entry.getValue()+"%' or ";
            }
            // sql的最后多or 关键字，需要单独处理  "select * from business where account like %zhang% or contact like %100% or"
            sql = sql.substring(0,sql.lastIndexOf("or"));
        }
        // "select * from business where account like %zhang% or contact like %100% limit 起始位置 , 条数"
        // 判断分页的参数是否携带
        Integer page = 1;
        Integer size = 2;
        if(StrUtil.isNotBlank(currentPage)){
            page = Integer.valueOf(currentPage);
        }
        if( StrUtil.isNotBlank(pageSize)){
            size = Integer.parseInt(pageSize);
        }
        sql = sql + "limit "+(page - 1) * size +" , " + size;
        System.out.println(sql);
        return Db.use(dataSource).query(sql, Business.class);
    }

    public Long selectCount() throws SQLException {
        String sql = "select count(*) from business";
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        if(rs.next()){
            return rs.getLong(1);
        }
        return null;
    }




}
